package dao;

import entity.Subject;
import util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author oldliu
 * @since 1.0
 */
//任何结果，负责返回
public class SubjectDaoImpl implements SubjectDao {
    @Override
    public int add(Subject s) {
        Connection conn = DBUtil.get();
        PreparedStatement p = null;
        String sql =
                "insert into s_subject(subjectname,classhour,gradeid)values(?,?,?)";
        try {
            p = conn.prepareStatement(sql);
            p.setString(1, s.getSubjectname());
            p.setInt(2, s.getClasshour());
            p.setLong(3, s.getGradeid());
            return p.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, p, conn);
        }
        return 0;
    }

    @Override
    public int update(Subject s) {
        Connection conn = DBUtil.get();
        PreparedStatement p = null;
        String sql =
                "update  s_subject set subjectname=?,classhour=?,gradeid=? where  id=?";
        try {
            p = conn.prepareStatement(sql);
            p.setString(1, s.getSubjectname());
            p.setInt(2, s.getClasshour());
            p.setLong(3, s.getGradeid());
            p.setLong(4, s.getId());
            return p.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, p, conn);
        }
        return 0;
    }

    @Override
    public int del(Long id) {
        Connection conn = DBUtil.get();
        PreparedStatement p = null;
        String sql ="delete from s_subject  where  id=?";
        try {
            p = conn.prepareStatement(sql);
            p.setLong(1, id);
            return p.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, p, conn);
        }
        return 0;
    }

    @Override
    public int del(Subject s) {
        return 0;
    }

    @Override//根据课程号，返回课程的全部信息
    public Subject get(Long id) {
        Connection conn = DBUtil.get();
        Subject data=null;
        PreparedStatement p = null;
        ResultSet rs=null;
        String sql ="select id,subjectname,classhour,gradeid from s_subject where id=?";
        try {
            p = conn.prepareStatement(sql);
            p.setLong(1,id);
            rs=p.executeQuery();
            if (rs.next()){
                //Subject subject = new Subject();
                //subject.setId(rs.getLong("id"));
                data=new Subject(rs.getLong("id"),rs.getLong("gradeid"),rs.getString("subjectname"),rs.getInt("classhour"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, p, conn);
        }
        return data;
    }

    //找不到，返回空集合对象
    @Override
    public List<Subject> findALL() {
        List<Subject> datas=new ArrayList<>();

        Connection conn = DBUtil.get();
        PreparedStatement p = null;
        ResultSet rs=null;
        String sql ="select id,subjectname,classhour,gradeid from s_subject";
        try {
            p = conn.prepareStatement(sql);
            rs=p.executeQuery();
            while (rs.next()){
                //Subject subject = new Subject();
                //subject.setId(rs.getLong("id"));
                datas.add(new Subject(rs.getLong("id"),rs.getLong("gradeid"),rs.getString("subjectname"),rs.getInt("classhour")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, p, conn);
        }
        return datas;
    }

    @Override
    public List<Subject> paging(int pageNo, int pageSize) {
        List<Subject> datas=new ArrayList<>();

        Connection conn = DBUtil.get();
        PreparedStatement p = null;
        ResultSet rs=null;
        String sql ="select id,subjectname,classhour,gradeid from s_subject limit ?,?";
        try {
            p = conn.prepareStatement(sql);
            p.setInt(1,(pageNo-1)*pageSize);
            p.setInt(2,pageSize);
            rs=p.executeQuery();
            while (rs.next()){
                datas.add(new Subject(rs.getLong("id"),rs.getLong("gradeid"),rs.getString("subjectname"),rs.getInt("classhour")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, p, conn);
        }
        return datas;
    }

    /**
     * 模糊查找，并且加上分页
     * @param subname
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Override
    public List<Subject> find(String subname, int pageNo, int pageSize) {
        List<Subject> datas=new ArrayList<>();

        Connection conn = DBUtil.get();
        PreparedStatement p = null;
        ResultSet rs=null;
        String sql ="select id,subjectname,classhour,gradeid from s_subject where subjectname like ? limit ?,?";
        try {
            p = conn.prepareStatement(sql);
            //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
            p.setString(1,"%"+subname+"%");
            p.setInt(2,(pageNo-1)*pageSize);
            p.setInt(3,pageSize);
            rs=p.executeQuery();
            while (rs.next()){
                datas.add(new Subject(rs.getLong("id"),rs.getLong("gradeid"),rs.getString("subjectname"),rs.getInt("classhour")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, p, conn);
        }
        return datas;
    }
}
